﻿using System;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PropertyExpression.Common;

namespace PropertyExpression.ControllableQuery.Demo
{
    class Program
    {
        static void Main()
        {
            var result = new {
                filter = Filter.Suggested, userIsAnonymous = false, userIsModerator = false, userId = 1,
                tagScoreRequiredToVote = 10, search = "%test%", tab = Tab.Newest, start = 0, pageSize = 20
            }.Apply(p => {
                Action<SqlCommand, StringBuilder> subQuery = (command, builder) => {
                    builder.Append(@"
FROM    TagSynonyms
        LEFT JOIN Tags SourceTags ON SourceTagName = SourceTags.Name
        LEFT JOIN Tags TargetTags ON TargetTagName = TargetTags.Name
        LEFT JOIN Users ON Users.Id = OwnerUserId
WHERE   1 = 1");
                    switch (p.filter)
                    {
                        case Filter.Active:
                            builder.Append(@"
        AND ApprovalDate IS NOT NULL");
                            break;
                        case Filter.Suggested:
                            builder.Append(@"
        AND ApprovalDate IS NULL");
                            if (!p.userIsAnonymous && !p.userIsModerator)
                            {
                                builder.Append(@"
        AND TargetTagName IN (
            SELECT  Name
            FROM    Tags
            WHERE   Id IN (SELECT   Id
                           FROM     UserTagTotals
                           WHERE    UserId = @userId
                                    AND TotalAnswerScort > @tagScoreRequiredToVote)
            )");
                                command.Parameters.AddWithValue("@userId", p.userId);
                                command.Parameters.AddWithValue("@tagScoreRequiredToVote", p.tagScoreRequiredToVote);
                            }
                            break;
                        case Filter.Merge:
                            builder.Append(@"
        AND ApprovalDate IS NOT NULL AND ISNULL(SourceTags.Count, 0) > 0");
                            break;
                    }
                    if (!string.IsNullOrEmpty(p.search))
                    {
                        builder.Append(@"
        AND (SourceTagName LIKE @search OR TargetTagName LIKE @search)");
                        command.Parameters.AddWithValue("@search", p.search);
                    }
                };

                var rowsCommand = new SqlCommand();
                var rowsBuilder = new StringBuilder(@"
SELECT  TagSynonyms.Id, SourceTagName, TargetTagName, OwnerUserId, ApprovalDate, TagSynonyms.CreationDate,
        AutoRenameCount, Score, SourceTags.Id AS SourceTagId, TargetTags.Id AS TargetTagId, Users.DisplayName");
                subQuery(rowsCommand, rowsBuilder);
                rowsBuilder.Append(@"
ORDER BY");
                switch (p.tab)
                {
                    case Tab.Newest:
                        rowsBuilder.Append(@"
        CreationDate DESC, Id");
                        break;
                    case Tab.Master:
                        rowsBuilder.Append(@"
        TargetTagName ASC, AutoRenameCount DESC, Id");
                        break;
                    case Tab.Synonym:
                        rowsBuilder.Append(@"
        SourceTagName ASC, AutoRenameCount DESC, Id");
                        break;
                    case Tab.Votes:
                        rowsBuilder.Append(@"
        Score DESC, TargetTagName ASC, AutoRenameCount DESC, Id");
                        break;
                    case Tab.Creator:
                        rowsBuilder.Append(@"
        DisplayName, Id");
                        break;
                    case Tab.Renames:
                        rowsBuilder.Append(@"
        AutoRenameCount DESC, TargetTagName, Id");
                        break;
                }
                rowsBuilder.Append(@"
OFFSET @start ROWS FETCH NEXT @pageSize ROWS ONLY
OPTION (OPTIMIZE FOR (@start = 0, @pageSize = 20))");
                rowsCommand.Parameters.AddWithValue("@start", p.start);
                rowsCommand.Parameters.AddWithValue("@pageSize", p.pageSize);
                rowsCommand.CommandText = rowsBuilder.ToString();

                var countCommand = new SqlCommand();
                var countBuilder = new StringBuilder();
                countBuilder.Append(@"
SELECT  COUNT(*)");
                subQuery(countCommand, countBuilder);
                countCommand.CommandText = countBuilder.ToString();

                return new {
                    rows = rowsCommand.Query<ITagSynonym>(),
                    count = countCommand.Query<int?>()
                };
            });
            var list = result.rows.ToList();
            var count = result.count.Single();
        }
    }
}
